package util;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicLong;

/**
 * 将文本文件的内容导入到excel中
 * 
 * @author root
 */
public class ExcelMerge {
	final static Logger logger = LoggerFactory.getLogger(ExcelMerge.class);
	static int rows = 65536;
	static String excelDefaultName = "";
	final static AtomicLong total = new AtomicLong(0);
	
	public static void main(String[] args) {
		File baseDir = new File("C:\\Users\\Administrator\\Desktop\\ni");
		File targetDir = new File("e:\\zuizhong1");
//		merge(baseDir);
		
//		File baseFile=new File(Configure.BASE_DIR+File.separator+Configure.N_RESULT+File.separator+Configure.N_FILTER);
//		File targetFile=new File(Configure.BASE_DIR+File.separator+Configure.N_RESULT+File.separator+Configure.N_EXCEL);
		merge(baseDir,targetDir);
		System.out.println("当前批次记录总量："+total.intValue());
	}
	
	public static void merge(File baseDir) {
		merge(baseDir,baseDir);
	}
	public static void merge(File baseDir, File targetDir) {
		Map<String, List<File>> group = new HashMap<String, List<File>>();
		String fileName = null;
		String names[] = null;
		if (baseDir.exists()) {
			for (File file : baseDir.listFiles()) {
				// 文件夹
				if (file.isFile() && file.getName().endsWith(".xls")) {
					fileName = file.getName();
					names = fileName.split("-");
					if (group.get(names[0]) == null) {
						group.put(names[0], new ArrayList());
					}
					group.get(names[0]).add(file);
				}
			}
			WritableWorkbook wwb;
			File file = null;
			for (String key : group.keySet()) {
				try {
					if(group.get(key).size()>0){
						Tools.checkAndMakeDirecotry(targetDir);
						file = new File(targetDir.getPath() + File.separator + key + ".xls");
						wwb = Workbook.createWorkbook(file);
						outex(group.get(key), wwb);
					}
				} catch (IOException e) {
					logger.error(Tools.exception2string(e));
					
				}
			}
		}
	}
	
	public static void outex(List<File> files, WritableWorkbook wwb) {
		int rowNum = 0;
		int colNum = 0;
		int sheetNo = 1;
		WritableSheet ws = wwb.createSheet("Sheet1", sheetNo++);
		Label lab = null;
		String temp = null;
		jxl.write.Number nlab = null;
		try {
			initTitle(ws, colNum, rowNum);
			rowNum = 1;
			for (File file : files) {
				logger.info(file.getPath());
				Workbook input = Workbook.getWorkbook(file);
				Sheet allSheet[] = input.getSheets();// 得到工作表数组
				for (Sheet sheet : allSheet) {
					for (int i = 1; i < sheet.getRows(); i++) {
						Cell cells[] = sheet.getRow(i);
						colNum = 0;
						for (Cell cell : cells) {
							if (colNum == 0) {
								if(StringUtils.isNotBlank(cell.getContents())){
									nlab = new jxl.write.Number(colNum++, rowNum, Integer.parseInt(cell.getContents()));
									ws.addCell(nlab);
								} else {
									nlab = new jxl.write.Number(colNum++, rowNum, rowNum);
									ws.addCell(nlab);
								}
							} else {
								lab = new Label(colNum++, rowNum, cell.getContents());
								ws.addCell(lab);
							}
						}
						rowNum++;
						total.incrementAndGet();
						if (rowNum >= rows) {
							ws = wwb.createSheet("Sheet " + (++sheetNo), sheetNo);
							initTitle(ws, colNum, rowNum);
							rowNum = 1;
						}
					}
				}
				input.close();
			}
			logger.info("合并文件数："+files.size()+"    合并后的总量：" + rowNum);
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			logger.error(Tools.exception2string(e));
			
		}

	}
	
	public static void initTitle(WritableSheet ws, int colNum, int rowNum) throws RowsExceededException, WriteException {
		Label l = new Label(colNum++, rowNum, "顺序号");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "区域");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "抓取时间");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "链接");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "标题");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "匹配关键字");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "内容");
		ws.addCell(l);
	}
}
